Before computers, numerical analyses, particularly financial ones, were usually prepared on an accountant's columnar pad or spreadsheet, with pencil and calculator in hand. By organising data into columns and rows, spreadsheets afford the rapid assimilation of information by a reader. The task of preparing a spreadsheet on paper, however, is not quite so fast. Instead, the process tends to be very slow, as each entry must be tediously calculated and entered into the spreadsheet. Since all calculations are the responsibility of the preparer, manually prepared spreadsheets are also prone to errors. Hence, preparation of spreadsheets by hand is slow, tedious, and unreliable.
With the advent of microcomputers, a solution was forthcoming in the form of “electronic spreadsheets.” Better known simply as “spreadsheets,” these software programs provide a computerised replacement for the traditional financial modelling tools: the accountant's columnar pad, pencil, and calculator. In some regards, spreadsheet programs are to those tools what word processors are to typewriters. Spreadsheets offer dramatic improvements in ease of creating, editing, and using financial models.
A typical spreadsheet program configures the memory of a computer to resemble the column/row or grid format of an accountant's columnar pad, thus providing a visible calculator for a user. Because this “pad” exists dynamically in the computer's memory, however, it differs from paper pads in several important ways. Locations in the electronic spreadsheet, for example, must be communicated to the computer in a format which it can understand. A common scheme for accomplishing this is to assign a number to each row in a spreadsheet, a letter to each column, and another letter to each sheet (or page) of the spreadsheet. To reference a location at column A and row 1 of the second page (i.e., the upper-left hand corner), for example, the user types in “B:A1”. In this manner, the spreadsheet defines an addressable storage location or “cell” at each intersection of a row with a column within a given page.
Data entry into an electronic spreadsheet occurs in much the same manner that information would be entered on an accountant's pad. After a screen cursor is positioned at a desired location, the user can enter alphanumeric information. Besides holding text and numeric information, however, spreadsheet cells can store special instructions or “formulas” specifying calculations to be performed on the numbers stored in spreadsheet cells. Such spreadsheet cells can also be defined and named as a range as long as they are arranged as a connex set of cells. A typical example of such a named range simply corresponds to a regular table found in an accountant's pad. In this fashion, range names can serve as variables in an equation, thereby allowing precise mathematical relationships to be defined between cells. The structure and operation of a spreadsheet program, including advanced functions such as functions and macros, are documented in the technical, trade, and patent literature. For an overview, see e.g., Cobb, S., Using Quattro Pro 2, Borland-OsbomeIMcGraw-Mll, 1990; and LeBlond, G. and Cobb, D., Using 1-2-3, Que corp., 1985. The disclosures of each of the foregoing are hereby incorporated by reference.
Electronic spreadsheets offer many advantages over their paper counterparts. For one, electronic spreadsheets are much larger (i.e., hold more information) than their paper counterparts; electronic spreadsheets having thousands or even millions of cells are not uncommon. Spreadsheet programs also allow users to perform “what-if” scenarios. After a set of computational relationships has been entered into a worksheet, thanks to imbedded formulas for instance, the spread of information can be recalculated using different sets of assumptions, with the results of each recalculation appearing almost instantaneously. Performing this operation manually, with paper and pencil, would require recalculating every relationship in the model with each change made. Thus, electronic spreadsheet systems were invented to solve “what-if” problems, that is, changing an input and seeing what happens to an output.
Electronic spreadsheet files have thus become today the tool of choice for manipulating data, most of the time numbers, and computing output information from input information which may occupy hundreds or thousands of row data records. The most classical and obvious example corresponds to a vertical table constituted by a plurality of horizontal records, and structured according to vertical fields. When a table is initialised within electronic spreadsheet files, the user has to specify for each table record the different fields belonging to the table. Some field are “input” fields which will receive row data, while other fields are “output” fields which will result from the input fields according to some formulas. The initialisation of input fields is most of the time difficult to automate as they are generally corresponding to different samples of a given variable. Nevertheless input field data are easy to specify as the user is just asked to type a value within the spreadsheet file. The initialisation of the output fields is less combersome as the same relationship (in fact a formula) is used across different records, so that the initialisation of a field within a given record can be copied onto the other records. Nevertheless as the data to be specified is a formula (instead of a row data), the complexity comes from the strict syntax to be followed when specifying formulas. The present invention proposes an original system and method for assisting the spreadsheet user to intialise the output fields of a table part of an electronic spreadsheet file. The proposed method outscore some conventional means already available in the field of electronic spreadsheets.
MS Excel
The Excel tool from Microsoft corporation includes means allowing a spreadsheet user to use a table label as the argument of a function. This feature is interesting in the sense that it relieves the spreadsheet user to master the way range of cells are addressed within an electronic spreadsheet. Nevertheless it presents the drawback of being only used as function arguments, requiring thus that the spreadsheet user do master the function call syntax. This feature is illustrated in the FIG. 3A showing some text extracted from the MS Excel on-line help.
Lotus 123
The 123 tool kit from Lotus Corporation defines the concept of SmartLabels. They allow user to associate a label with a function according to a predefined relationship. Facilities are made available within 123 to manage such relationships (for creating, editing, or cancelling them). This feature is interesting in the sense that it only asks the spreadsheet user to learn the SmartLabels (which are very intuitive, like the label “total” to sum the various elements of a given field) instead of the function syntax. Nevertheless it presents the drawback of only facilitating the specification of function, but without specifying arguments other than using the reserved word “list” to represent a range of cells. So the interest is limited to conventional functions like summing, or averaging a range. The FIG. 3B shows an extract of the on-line 123 help articulating the SmartLabel usage, whereas the FIG. 3C illustrates the corresponding SmartLabel Editor interface.
The limitations of the conventional tools introduced here above become quickly unbearable when an “average” spreadsheet user (or simply a spreadsheet beginner) wishes to quickly build tables without having to master the syntax of the formula language available within the electronic spreadsheet environment.
IBM ICU
The Interactive Chart Utility (ICU) is a module being part of the GDDM-PGF (5668-812) product from IBM, allows the user to draw charts on his screen and print them. This tool allows entry of data with columns and rows, the column values representing the X axis and the different Y axis values. Each Y axis column represents the Y axis values of one curve to be drawn. To facilitate the data entry, the columns automatically receive as a label, a simple variable name, X (for X axis column values), Y1, Y2, . . . Yn . . . For the different Y axis column values.
It is possible to use a combination of values from other columns to create a new column of Y axis value by entering the combination on a command line dedicated to each column. The combination is limited to a basic operation such as multiplication, division, addition substraction, the operands being the column labels. The argument and operand are entered step by step.
It is possible also to modify the values in one existing column by entering a combination as described here above. It is possible to modify the value of an existing row by introducing on a command line of the corresponding row numerical step by step simple operations. It is not possible to use a combination of other row labels used as variables because the rows do not receive row labels. It is not possible also to create new rows as it is possible to do it with the columns.
There is a need to facilitate the data entry in tables for all the kind of data management applications described here above. It should be possible to create or modify rows or columns by combining numerical values or row or column values into a function using the row or column label names as variables.